using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	/// <summary>
	/// Class representing a loan entry in the database. 
	/// </summary>
	public class Loan
	{
		private int m_loan_ID;
		private int m_copy_ID;
		private int m_user_ID;
		private string m_book_isbn;
		private DateTime m_datestart;
		private DateTime m_dateend;
		private DateTime m_datereturned;
		private string m_bookTitle;
		private string m_string_datestart;
		private string m_string_dateend;
		private string m_string_datereturned;
		private bool m_isOverdue;
		public static int loan_range = 7;
		
		public Loan ()
		{
		}
		
		public Loan (int loan_ID)
		{
			m_loan_ID = loan_ID;
			getData();
		}
		
		public int loan_ID
		{
			set {
				m_loan_ID = value;
				getData();
			}
			get {
				return m_loan_ID;
			}
		}
		
		public int copy_ID
		{
			set {
				m_copy_ID = value;
			}
			get {
				return m_copy_ID;
			}
		}
		
		public string book_isbn
		{
			set {
				m_book_isbn = value;
			}
			get {
				return m_book_isbn;
			}
		}
		
		public int user_ID
		{
			set {
				m_user_ID = value;
			}
			get {
				return m_user_ID;
			}
		}
		
		public DateTime datestart
		{
			set {
				m_datestart = value;
			}
			get {
				return m_datestart;
			}
		}
		
		public DateTime dateend
		{
			set {
				m_dateend = value;
			}
			get {
				return m_dateend;
			}
		}
		
		public DateTime datereturned
		{
			set {
				m_datereturned = value;
			}
			get {
				return m_datereturned;
			}
		}
		
		public string bookTitle
		{
			set {
				m_bookTitle = value;
			}
			get {
				return m_bookTitle;
			}
		}
		
		public string string_datestart
		{
			set {
				
			}
			get {
				return m_string_datestart;
			}
		}
		
		public string string_dateend
		{
			set {
			}
			get {
				return m_string_dateend;
			}
		}
		
		public string string_datereturned
		{
			set {
			}
			get {
				return m_string_datereturned;
			}
		}
		
		public bool isOverdue
		{
			set{
			}
			get{
				return m_isOverdue;
			}
		}
		
		private void getData()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM loan WHERE loan_ID = '" + this.m_loan_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);	
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					this.m_copy_ID = (int) reader["copy_ID"];
					this.m_user_ID = (int) reader["user_ID"];
					this.m_datestart = (DateTime) reader["datestart"];
					this.m_string_datestart = this.m_datestart.ToString("MMM d, yyyy");
					this.m_dateend = (DateTime) reader["dateend"];
					this.m_string_dateend = this.m_dateend.ToString("MMM d, yyyy");
					if (!reader.IsDBNull(4)){
						this.m_datereturned = (DateTime) reader["datereturned"];
						this.m_string_datereturned = this.m_datereturned.ToString("MMM d, yyyy");
					}
					else{
						this.m_string_datereturned = "";
						if (this.m_dateend < DateTime.Now){
							this.m_isOverdue = true;
						}
						else
							this.m_isOverdue = false;
					}
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();		
			Book dummy = Copy.getBook(this.m_copy_ID);
			this.m_bookTitle = dummy.title;
			this.m_book_isbn = dummy.isbn;
		}
		
		/// <summary>
		/// Add a loan entry in the database. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the book.
		/// </param>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user.
		/// </param>
		/// <param name="datestart">
		/// A <see cref="DateTime"/> The starting date and time of the loan.
		/// </param>
		/// <param name="dateend">
		/// A <see cref="DateTime"/> The ending date and time of the loan.
		/// </param>
		/// <param name="datereturned">
		/// A <see cref="DateTime"/> The date the book was returned.
		/// </param>		
		public static void addLoan(int copy_ID, int user_ID, DateTime datestart, DateTime dateend, DateTime datereturned)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO loan (copy_ID, user_ID, datestart, dateend, datereturned) "
					+ "VALUES ('"+copy_ID+"','"+user_ID+"','"+datestart+"','"+dateend+"','"+datereturned+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
			
		public static void adminAddLoan(int copy_ID, int user_ID, DateTime datestart, DateTime dateend)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO loan (copy_ID, user_ID, datestart, dateend) "
					+ "VALUES ('"+copy_ID+"','"+user_ID+"','"+datestart+"','"+dateend+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Deletes a loan entry in the database. 
		/// </summary>
		/// <param name="loan_ID">
		/// A <see cref="System.Int32"/> The loan_ID of the loan entry.
		/// </param>
		public static void deleteLoan(int loan_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();				
				string statement = "DELETE FROM loan where loan_ID='"+loan_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Edits a loan entry in the database. 
		/// </summary>
		/// <param name="loan_ID">
		/// A <see cref="System.Int32"/> The loan_ID of the loan.
		/// </param>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the copy of the book.
		/// </param>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user.
		/// </param>
		/// <param name="datestart">
		/// A <see cref="DateTime"/> The starting date of the loan.
		/// </param>
		/// <param name="dateend">
		/// A <see cref="DateTime"/> The end date of the loan.
		/// </param>
		/// <param name="datereturned"> The date the user returned the book.
		/// A <see cref="DateTime"/>
		/// </param>
		public static void editLoan(int loan_ID, int copy_ID, int user_ID, DateTime datestart, DateTime dateend, DateTime datereturned)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();				
				string newStatement = "UPDATE loan SET copy_ID='"+copy_ID+"', user_ID='"+user_ID+"', datestart='"+datestart
					+ "', dateend='"+dateend+"', datereturned='"+datereturned+"' WHERE loan_ID='"+loan_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Edits the datereturned column of a loan entry. 
		/// </summary>
		/// <param name="loan_ID">
		/// A <see cref="System.Int32"/> The loan_ID of the loan.
		/// </param>
		/// <param name="datereturned">
		/// A <see cref="DateTime"/> The updated datereturned of the loan.
		/// </param>
		public static void returnLoan(int loan_ID, DateTime datereturned)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();				
				string newStatement = "UPDATE loan datereturned='"+datereturned+"' WHERE loan_ID='"+loan_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		
	}
}